﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.Practices.Unity;
using VLUSM_BusinessLogic.Helpers;
using VLUSM_BusinessLogic.DataTransferObjects;
using VLUSM_DataAccess;
using System.Data.SqlClient;
using System.Data.EntityClient;
using System.Data;
using System.Collections.ObjectModel;

namespace VLUSM_BusinessLogic
{
    public class StudentManager
    {
        IUnityContainer container;

        EntityConnection mainDataConnection;
        EntityConnection DVHCConnection;
        EntityConnection diemConnection;
        EntityConnection viewConnection;

        public StudentManager(IUnityContainer container)
        {
            this.container = container;
            this.Initialize();
        }

        #region CauLacBoThamGia Method
        public CauLacBoThamGiaDTO GetCauLacBoThamGiaByID(int IDFilter)
        {
            using (var context = container.Resolve<MainDataContainer>())
            {
                var result = context.CauLacBoThamGia.Include("CauLacBo").Single(v => v.CauLacBoID == IDFilter);
                return Converter.ConvertToCauLacBoThamGiaDTO(result);
            }
        }
        public ObservableCollection<CauLacBoThamGiaDTO> GetCauLacBoThamGiaBySinhVienID(int SinhVienIDFilter)
        {
            using (var context = container.Resolve<MainDataContainer>())
            {
                var result = context.CauLacBoThamGia.Include("CauLacBo").Where(v => v.SinhVien.ID == SinhVienIDFilter);
                var oc = new ObservableCollection<CauLacBoThamGia>(result);
                return Converter.ConvertToCauLacBoThamGiaDTOList(oc);
            }
        }

        public void SaveCauLacBoThamGia(CauLacBoThamGiaDTO objdto)
        {
            var obj = Converter.ConvertToCauLacBoThamGia(objdto);
            using (var context = container.Resolve<MainDataContainer>())
            {
                var sinhVienQuery = context.SinhVien.Single(s => s.ID == objdto.SinhVienID);
                sinhVienQuery.CauLacBoThamGias.Add(obj);
                context.SaveChanges();
            }
        }

        public void SaveCauLacBoThamGiaList(ObservableCollection<CauLacBoThamGiaDTO> dtolist)
        {
            var list = Converter.ConvertToCauLacBoThamGiaList(dtolist);
            using (var context = container.Resolve<MainDataContainer>())
            {
                foreach (var obj in list)
                {
                    context.CauLacBoThamGia.Attach(obj);
                    if (obj.ID != 0)
                    {
                        context.ObjectStateManager.ChangeObjectState(obj, EntityState.Modified);
                    }
                    else
                    {
                        context.ObjectStateManager.ChangeObjectState(obj, EntityState.Added);
                    }
                }
                context.SaveChanges();
            }
        }

        public void RemoveCauLacBoThamGia(CauLacBoThamGiaDTO objdto)
        {
            var obj = Converter.ConvertToCauLacBoThamGia(objdto);
            using (var context = container.Resolve<MainDataContainer>())
            {
                context.CauLacBoThamGia.Attach(obj);
                context.ObjectStateManager.ChangeObjectState(obj, EntityState.Deleted);
                context.SaveChanges();
            }
        }

        public void RemoveCauLacBoThamGiaByID(int IDFilter)
        {
            using (var context = container.Resolve<MainDataContainer>())
            {
                var obj = context.CauLacBoThamGia.Single(t => t.ID == IDFilter);
                context.CauLacBoThamGia.Attach(obj);
                context.ObjectStateManager.ChangeObjectState(obj, EntityState.Deleted);
                context.SaveChanges();
            }
        }
        #endregion

        #region HocBong Method
        public HocBongDTO GetHocBongByID(int IDFilter)
        {
            using (var context = container.Resolve<MainDataContainer>())
            {
                var result = context.HocBong.Single(h => h.ID == IDFilter);
                return Converter.ConvertToHocBongDTO(result);
            }
        }

        public ObservableCollection<HocBongDTO> GetHocBongBySinhVienID(int SinhVienIDFilter)
        {
            using (var context = container.Resolve<MainDataContainer>())
            {
                var result = context.HocBong.Where(v => v.SinhVien.ID == SinhVienIDFilter);
                var oc = new ObservableCollection<HocBong>(result);
                return Converter.ConvertToHocBongDTOList(oc);
            }
        }

        public void SaveHocBong(HocBongDTO objdto)
        {
            var obj = Converter.ConvertToHocBong(objdto);
            using (var context = container.Resolve<MainDataContainer>())
            {
                var sinhVienQuery = context.SinhVien.Single(s => s.ID == objdto.SinhVienID);
                sinhVienQuery.HocBongs.Add(obj);
                context.SaveChanges();
            }
        }

        public void SaveHocBongList(ObservableCollection<HocBongDTO> dtolist)
        {
            var list = Converter.ConvertToHocBongList(dtolist);
            using (var context = container.Resolve<MainDataContainer>())
            {
                foreach (var obj in list)
                {
                    context.HocBong.Attach(obj);
                    if (obj.ID != 0)
                    {
                        context.ObjectStateManager.ChangeObjectState(obj, EntityState.Modified);
                    }
                    else
                    {
                        context.ObjectStateManager.ChangeObjectState(obj, EntityState.Added);
                    }
                }
                context.SaveChanges();
            }
        }

        public void RemoveHocBong(HocBongDTO objdto)
        {
            var obj = Converter.ConvertToHocBong(objdto);
            using (var context = container.Resolve<MainDataContainer>())
            {
                context.HocBong.Attach(obj);
                context.ObjectStateManager.ChangeObjectState(obj, EntityState.Deleted);
                context.SaveChanges();
            }
        }

        public void RemoveHocBongByID(int IDFilter)
        {
            using (var context = container.Resolve<MainDataContainer>())
            {
                var obj = context.HocBong.Single(t => t.ID == IDFilter);
                context.HocBong.Attach(obj);
                context.ObjectStateManager.ChangeObjectState(obj, EntityState.Deleted);
                context.SaveChanges();
            }
        }
        #endregion

        #region HoSoDaNop Methods
        public HoSoDaNopDTO GetHoSoDaNopByID(int IDFilter)
        {
            using (var context = container.Resolve<MainDataContainer>())
            {
                var result = context.HoSoDaNop.Single(h => h.ID == IDFilter);
                return Converter.ConvertToHoSoDaNopDTO(result);
            }
        }

        public ObservableCollection<HoSoDaNopDTO> GetHoSoDaNopBySinhVienID(int SinhVienIDFilter)
        {
            using (var context = container.Resolve<VLUSMEntities>())
            {
                var result = context.HoSoNopLists.Where(h => h.SinhVienID == SinhVienIDFilter);
                var hsnoc = new ObservableCollection<HoSoNopList>(result);
                var mainoc = Converter.ConvertToHoSoDaNopDTOListFromView(hsnoc);
                foreach (var hoso in mainoc)
                {
                    using (var context2 = container.Resolve<MainDataContainer>())
                    {
                        var hs = context2.LoaiHoSo.Single(h => h.ID == hoso.LoaiHoSoID);
                        hoso.TenLoaiHoSo = hs.TenGoi;
                    }
                    if (hoso.ID == 0)
                    {
                        hoso.IsTrue = false;
                    }
                    else
                    {
                        hoso.IsTrue = true;
                    }
                }
                return mainoc;
            }
        }

        public void SetHoSoDaNopListForSinhVien(int SinhVienID, ObservableCollection<HoSoDaNopDTO> listDTO)
        {
            using (var context = container.Resolve<MainDataContainer>())
            {
                foreach (var obj in listDTO)
                {
                    HoSoDaNop hoSo = new HoSoDaNop();
                    hoSo.SinhVienID = SinhVienID;
                    hoSo.LoaiHoSoID = obj.LoaiHoSoID;
                    hoSo.NgayNhan = DateTime.Today;
                    hoSo.NhanVienNhan = obj.NhanVienNhan;
                    context.HoSoDaNop.Attach(hoSo);
                    context.ObjectStateManager.ChangeObjectState(hoSo, EntityState.Added);

                }
                context.SaveChanges();
            }
        }

        public void UnsetHoSoDaNopListForSinhVien(int SinhVienID, ObservableCollection<HoSoDaNopDTO> listDTO)
        {
            using (var context = container.Resolve<MainDataContainer>())
            {
                var sinhVienQuery = context.SinhVien.Single(s => s.ID == SinhVienID);

                foreach (var hoSoDaNopdto in listDTO)
                {
                    var hoSoDaNop = context.HoSoDaNop.FirstOrDefault(h => h.ID == hoSoDaNopdto.ID);
                    context.DeleteObject(hoSoDaNop);
                }
                context.SaveChanges();
            }
        }
        #endregion

        #region KhenThuong Methods
        public KhenThuongDTO GetKhenThuongByID(int IDFilter)
        {
            using (var context = container.Resolve<MainDataContainer>())
            {
                var result = context.KhenThuongs.Single(k => k.ID == IDFilter);
                return Converter.ConvertToKhenThuongDTO(result);
            }
        }

        public ObservableCollection<KhenThuongDTO> GetKhenThuongBySinhVienID(int SinhVienIDFilter)
        {
            using (var context = container.Resolve<MainDataContainer>())
            {
                var result = context.KhenThuongs.Where(k => k.SinhVienID == SinhVienIDFilter);
                var oc = new ObservableCollection<KhenThuong>(result);
                return Converter.ConvertToKhenThuongDTOList(oc);
            }
        }

        public void SaveKhenThuong(KhenThuongDTO objdto)
        {
            var obj = Converter.ConvertToKhenThuong(objdto);
            using (var context = container.Resolve<MainDataContainer>())
            {
                var sinhVienQuery = context.SinhVien.Single(s => s.ID == objdto.SinhVienID);
                sinhVienQuery.KhenThuongs.Add(obj);
                context.SaveChanges();
            }
        }

        public void SaveKhenThuongList(ObservableCollection<KhenThuongDTO> dtolist)
        {
            var list = Converter.ConvertToKhenThuongList(dtolist);
            using (var context = container.Resolve<MainDataContainer>())
            {
                foreach (var obj in list)
                {
                    context.KhenThuongs.Attach(obj);
                    if (obj.ID != 0)
                    {
                        context.ObjectStateManager.ChangeObjectState(obj, EntityState.Modified);
                    }
                    else
                    {
                        context.ObjectStateManager.ChangeObjectState(obj, EntityState.Added);
                    }
                }
                context.SaveChanges();
            }
        }

        public void RemoveKhenThuong(KhenThuongDTO objdto)
        {
            var obj = Converter.ConvertToKhenThuong(objdto);
            using (var context = container.Resolve<MainDataContainer>())
            {
                context.KhenThuongs.Attach(obj);
                context.ObjectStateManager.ChangeObjectState(obj, EntityState.Deleted);
                context.SaveChanges();
            }
        }

        public void RemoveKhenThuongByID(int IDFilter)
        {
            using (var context = container.Resolve<MainDataContainer>())
            {
                var obj = context.KhenThuongs.Single(k => k.ID == IDFilter);
                context.KhenThuongs.Attach(obj);
                context.ObjectStateManager.ChangeObjectState(obj, EntityState.Deleted);
                context.SaveChanges();
            }
        }
        #endregion

        #region KyLuat Methods
        public KyLuatDTO GetKyLuatByID(int IDFilter)
        {
            using (var context = container.Resolve<MainDataContainer>())
            {
                var result = context.KyLuats.Single(k => k.ID == IDFilter);
                return Converter.ConvertToKyLuatDTO(result);
            }
        }

        public ObservableCollection<KyLuatDTO> GetKyLuatBySinhVienID(int SinhVienIDFilter)
        {
            using (var context = container.Resolve<MainDataContainer>())
            {
                var result = context.KyLuats.Where(k => k.SinhVienID == SinhVienIDFilter);
                var oc = new ObservableCollection<KyLuat>(result);
                return Converter.ConvertToKyLuatDTOList(oc);
            }
        }

        public void SaveKyLuat(KyLuatDTO objdto)
        {
            var obj = Converter.ConvertToKyLuat(objdto);
            using (var context = container.Resolve<MainDataContainer>())
            {
                var sinhVienQuery = context.SinhVien.Single(s => s.ID == objdto.SinhVienID);
                sinhVienQuery.KyLuats.Add(obj);
                context.SaveChanges();
            }
        }

        public void SaveKyLuatList(ObservableCollection<KyLuatDTO> dtolist)
        {
            var list = Converter.ConvertToKyLuatList(dtolist);
            using (var context = container.Resolve<MainDataContainer>())
            {
                foreach (var obj in list)
                {
                    context.KyLuats.Attach(obj);
                    if (obj.ID != 0)
                    {
                        context.ObjectStateManager.ChangeObjectState(obj, EntityState.Modified);
                    }
                    else
                    {
                        context.ObjectStateManager.ChangeObjectState(obj, EntityState.Added);
                    }
                }
                context.SaveChanges();
            }
        }

        public void RemoveKyLuat(KyLuatDTO objdto)
        {
            var obj = Converter.ConvertToKyLuat(objdto);
            using (var context = container.Resolve<MainDataContainer>())
            {
                context.KyLuats.Attach(obj);
                context.ObjectStateManager.ChangeObjectState(obj, EntityState.Deleted);
                context.SaveChanges();
            }
        }

        public void RemoveKyLuatByID(int IDFilter)
        {
            using (var context = container.Resolve<MainDataContainer>())
            {
                var obj = context.KyLuats.Single(k => k.ID == IDFilter);
                context.KyLuats.Attach(obj);
                context.ObjectStateManager.ChangeObjectState(obj, EntityState.Deleted);
                context.SaveChanges();
            }
        }
        #endregion

        #region HoatDongDoanTheThamGia Methods
        public HoatDongDoanTheThamGiaDTO GetHoatDongDoanTheThamGiaByID(int IDFilter)
        {
            using (var context = container.Resolve<MainDataContainer>())
            {
                var result = context.HoatDongDoanTheThamGias.Single(h => h.ID == IDFilter);
                return Converter.ConvertToHoatDongDoanTheThamGiaDTO(result);
            }
        }

        public ObservableCollection<HoatDongDoanTheThamGiaDTO> GetHoatDongDoanTheThamGiaBySinhVienID(int SinhVienIDFilter)
        {
            using (var context = container.Resolve<MainDataContainer>())
            {
                var result = context.HoatDongDoanTheThamGias.Where(h => h.SinhVienID == SinhVienIDFilter);
                var oc = new ObservableCollection<HoatDongDoanTheThamGia>(result);
                return Converter.ConvertToHoatDongDoanTheThamGiaDTOList(oc);
            }
        }

        public void SaveHoatDongDoanTheThamGia(HoatDongDoanTheThamGiaDTO objdto)
        {
            var obj = Converter.ConvertToHoatDongDoanTheThamGia(objdto);
            using (var context = container.Resolve<MainDataContainer>())
            {
                var sinhVienQuery = context.SinhVien.Single(s => s.ID == objdto.SinhVienID);
                sinhVienQuery.HoatDongDoanTheThamGias.Add(obj);
                context.SaveChanges();
            }
        }

        public void SaveHoatDongDoanTheThamGiaList(ObservableCollection<HoatDongDoanTheThamGiaDTO> dtolist)
        {
            var list = Converter.ConvertToHoatDongDoanTheThamGiaList(dtolist);
            using (var context = container.Resolve<MainDataContainer>())
            {
                foreach (var obj in list)
                {
                    context.HoatDongDoanTheThamGias.Attach(obj);
                    if (obj.ID != 0)
                    {
                        context.ObjectStateManager.ChangeObjectState(obj, EntityState.Modified);
                    }
                    else
                    {
                        context.ObjectStateManager.ChangeObjectState(obj, EntityState.Added);
                    }
                }
                context.SaveChanges();
            }
        }

        public void RemoveHoatDongDoanTheThamGia(HoatDongDoanTheThamGiaDTO objdto)
        {
            var obj = Converter.ConvertToHoatDongDoanTheThamGia(objdto);
            using (var context = container.Resolve<MainDataContainer>())
            {
                context.HoatDongDoanTheThamGias.Attach(obj);
                context.ObjectStateManager.ChangeObjectState(obj, EntityState.Deleted);
                context.SaveChanges();
            }
        }

        public void RemoveHoatDongDoanTheThamGiaByID(int IDFilter)
        {
            using (var context = container.Resolve<MainDataContainer>())
            {
                var obj = context.HoatDongDoanTheThamGias.FirstOrDefault(k => k.ID == IDFilter);
                context.DeleteObject(obj);
                context.SaveChanges();
            }
        }
        #endregion

        #region NghienCuuKhoaHoc Methods
        public NghienCuuKhoaHocDTO GetNghienCuuKhoaHocByID(int IDFilter)
        {
            using (var context = container.Resolve<MainDataContainer>())
            {
                var result = context.NghienCuuKhoaHoc.Single(n => n.ID == IDFilter);
                return Converter.ConvertToNghienCuuKhoaHocDTO(result);
            }
        }

        public ObservableCollection<NghienCuuKhoaHocDTO> GetNghienCuuKhoaHocBySinhVienID(int SinhVienIDFilter)
        {
            using (var context = container.Resolve<MainDataContainer>())
            {
                var result = context.NghienCuuKhoaHoc.Where(n => n.SinhVienID == SinhVienIDFilter);
                var oc = new ObservableCollection<NghienCuuKhoaHoc>(result);
                return Converter.ConvertToNghienCuuKhoaHocDTOList(oc);
            }
        }

        public void SaveNghienCuuKhoaHoc(NghienCuuKhoaHocDTO objdto)
        {
            var obj = Converter.ConvertToNghienCuuKhoaHoc(objdto);
            using (var context = container.Resolve<MainDataContainer>())
            {
                var sinhVienQuery = context.SinhVien.Single(s => s.ID == objdto.SinhVienID);
                sinhVienQuery.NghienCuuKhoaHocs.Add(obj);
                context.SaveChanges();
            }
        }

        public void SaveNghienCuuKhoaHocList(ObservableCollection<NghienCuuKhoaHocDTO> dtolist)
        {
            var list = Converter.ConvertToNghienCuuKhoaHocList(dtolist);
            using (var context = container.Resolve<MainDataContainer>())
            {
                foreach (var obj in list)
                {
                    context.NghienCuuKhoaHoc.Attach(obj);
                    if (obj.ID != 0)
                    {
                        context.ObjectStateManager.ChangeObjectState(obj, EntityState.Modified);
                    }
                    else
                    {
                        context.ObjectStateManager.ChangeObjectState(obj, EntityState.Added);
                    }
                }
                context.SaveChanges();
            }
        }

        public void RemoveNghienCuuKhoaHoc(NghienCuuKhoaHocDTO objdto)
        {
            var obj = Converter.ConvertToNghienCuuKhoaHoc(objdto);
            using (var context = container.Resolve<MainDataContainer>())
            {
                context.NghienCuuKhoaHoc.Attach(obj);
                context.ObjectStateManager.ChangeObjectState(obj, EntityState.Deleted);
                context.SaveChanges();
            }
        }

        public void RemoveNghienCuuKhoaHocByID(int IDFilter)
        {
            using (var context = container.Resolve<MainDataContainer>())
            {
                var obj = context.NghienCuuKhoaHoc.Single(n => n.ID == IDFilter);
                context.NghienCuuKhoaHoc.Attach(obj);
                context.ObjectStateManager.ChangeObjectState(obj, EntityState.Deleted);
                context.SaveChanges();
            }
        }
        #endregion

        #region NhatKy Methods
        public NhatKyDTO GetNhatKyByID(int IDFilter)
        {
            using (var context = container.Resolve<MainDataContainer>())
            {
                var result = context.NhatKy.Single(n => n.ID == IDFilter);
                return Converter.ConvertToNhatKyDTO(result);
            }
        }

        public ObservableCollection<NhatKyDTO> GetNhatKyBySinhVienID(int SinhVienIDFilter)
        {
            using (var context = container.Resolve<MainDataContainer>())
            {
                var result = context.NhatKy.Where(n => n.SinhVienID == SinhVienIDFilter);
                var oc = new ObservableCollection<NhatKy>(result);
                return Converter.ConvertToNhatKyDTOList(oc);
            }
        }

        public void SaveNhatKy(NhatKyDTO objdto)
        {
            var obj = Converter.ConvertToNhatKy(objdto);
            using (var context = container.Resolve<MainDataContainer>())
            {
                var sinhVienQuery = context.SinhVien.Single(s => s.ID == objdto.SinhVienID);
                sinhVienQuery.NhatKies.Add(obj);
                context.SaveChanges();
            }
        }

        public void SaveNhatKyList(ObservableCollection<NhatKyDTO> dtolist)
        {
            var list = Converter.ConvertToNhatKyList(dtolist);
            using (var context = container.Resolve<MainDataContainer>())
            {
                foreach (var obj in list)
                {
                    context.NhatKy.Attach(obj);
                    if (obj.ID != 0)
                    {
                        context.ObjectStateManager.ChangeObjectState(obj, EntityState.Modified);
                    }
                    else
                    {
                        context.ObjectStateManager.ChangeObjectState(obj, EntityState.Added);
                    }
                }
                context.SaveChanges();
            }
        }

        public void RemoveNhatKy(NhatKyDTO objdto)
        {
            var obj = Converter.ConvertToNhatKy(objdto);
            using (var context = container.Resolve<MainDataContainer>())
            {
                context.NhatKy.Attach(obj);
                context.ObjectStateManager.ChangeObjectState(obj, EntityState.Deleted);
                context.SaveChanges();
            }
        }

        public void RemoveNhatKyByID(int IDFilter)
        {
            using (var context = container.Resolve<MainDataContainer>())
            {
                var obj = context.NhatKy.Single(t => t.ID == IDFilter);
                context.NhatKy.Attach(obj);
                context.ObjectStateManager.ChangeObjectState(obj, EntityState.Deleted);
                context.SaveChanges();
            }
        }
        #endregion

        #region SinhVien Methods
        public ObservableCollection<SinhVienBasicDTO> GetSinhVienList()
        {
            using (var context = container.Resolve<MainDataContainer>())
            {
                var result = context.SinhVien;
                var oc = new ObservableCollection<SinhVien>(result);
                return Converter.ConvertToSinhVienBasicDTOList(oc);
            }
        }

        public SinhVienBasicDTO GetSinhVienByID(int IDFilter)
        {
            using (var context = container.Resolve<MainDataContainer>())
            {
                var result = context.SinhVien.Single(s => s.ID == IDFilter);
                return Converter.ConvertToSinhVienBasicDTO(result);
            }
        }

        public ObservableCollection<SinhVienBasicDTO> SearchSinhVien(string Query)
        {
            using (var context = container.Resolve<MainDataContainer>())
            {
                var result = from s in context.SinhVien
                             let fullname = s.HoSinhVien + " " + s.TenSinhVien
                             let maso = s.MaSinhVien
                             where fullname.Contains(Query) || maso.Contains(Query)
                             select s;
                var oc = new ObservableCollection<SinhVien>(result);
                return Converter.ConvertToSinhVienBasicDTOList(oc);
            }
        }

        public ObservableCollection<SinhVienBasicDTO> SearchSinhVienByDonVi(string Query, int DonViID)
        {
            using (var context = container.Resolve<MainDataContainer>())
            {
                if (!String.IsNullOrEmpty(Query))
                {
                    var result = from s in context.SinhVien
                                 let fullname = s.HoSinhVien + " " + s.TenSinhVien
                                 let maso = s.MaSinhVien
                                 where (fullname.Contains(Query) && s.Lop.DonViID == DonViID) || (maso.Contains(Query) && s.Lop.DonViID == DonViID)
                                 select s;
                    var oc = new ObservableCollection<SinhVien>(result);
                    return Converter.ConvertToSinhVienBasicDTOList(oc);
                }
                else
                {
                    var result = context.SinhVien.Where(s => s.Lop.DonViID == DonViID);
                    var oc = new ObservableCollection<SinhVien>(result);
                    return Converter.ConvertToSinhVienBasicDTOList(oc);
                }
            }
        }

        public ObservableCollection<SinhVienBasicDTO> SearchSinhVienByLop(string Query, int LopID)
        {
            using (var context = container.Resolve<MainDataContainer>())
            {
                if (!String.IsNullOrEmpty(Query))
                {
                    var result = from s in context.SinhVien
                                 let fullname = s.HoSinhVien + " " + s.TenSinhVien
                                 let maso = s.MaSinhVien
                                 where (fullname.Contains(Query) && s.Lop.ID == LopID) || (maso.Contains(Query) && s.Lop.ID == LopID)
                                 select s;
                    var oc = new ObservableCollection<SinhVien>(result);
                    return Converter.ConvertToSinhVienBasicDTOList(oc);
                }
                else
                {
                    var result = context.SinhVien.Where(s => s.LopID == LopID);
                    var oc = new ObservableCollection<SinhVien>(result);
                    return Converter.ConvertToSinhVienBasicDTOList(oc);
                }
            }
        }

        public ObservableCollection<SinhVienBasicDTO> GetSinhVienByDonViID(int DonViIDFilter)
        {
            using (var context = container.Resolve<MainDataContainer>())
            {
                var result = context.SinhVien.Where(s => s.Lop.DonViID == DonViIDFilter);
                var oc = new ObservableCollection<SinhVien>(result);
                return Converter.ConvertToSinhVienBasicDTOList(oc);
            }
        }

        public ObservableCollection<SinhVienBasicDTO> GetSinhVienByLopID(int LopIDFilter)
        {
            using (var context = container.Resolve<MainDataContainer>())
            {
                var result = context.SinhVien.Where(s => s.LopID == LopIDFilter);
                var oc = new ObservableCollection<SinhVien>(result);
                return Converter.ConvertToSinhVienBasicDTOList(oc);
            }
        }

        public ObservableCollection<SinhVienBasicDTO> GetSinhVienByTenDonVi(string TenDonViFilter)
        {
            using (var context = container.Resolve<MainDataContainer>())
            {
                var result = context.SinhVien.Where(s => s.Lop.DonVi.TenGoi == TenDonViFilter);
                var oc = new ObservableCollection<SinhVien>(result);
                return Converter.ConvertToSinhVienBasicDTOList(oc);
            }
        }

        public ObservableCollection<SinhVienBasicDTO> GetSinhVienByTenLop(string TenLopFilter)
        {
            using (var context = container.Resolve<MainDataContainer>())
            {
                var result = context.SinhVien.Where(s => s.Lop.TenGoi == TenLopFilter);
                var oc = new ObservableCollection<SinhVien>(result);
                return Converter.ConvertToSinhVienBasicDTOList(oc);
            }
        }

        public SinhVienDetailDTO GetSinhVienDetailByID(int IDFilter)
        {
            using (var context = container.Resolve<MainDataContainer>())
            {
                var result = context.SinhVien.Single(s => s.ID == IDFilter);
                var sinhvien = Converter.ConvertToSinhVienDetail(result);
                //var danhSachKyLuat = new ObservableCollection<KyLuat>(context.KyLuats.Where(k => k.SinhVienID == IDFilter));
                //var danhSachKhenThuong = new ObservableCollection<KhenThuong>(context.KhenThuongs.Where(k => k.SinhVienID == IDFilter));
                //var danhSachThamGiaOlympic = new ObservableCollection<ThamGiaOlympic>(context.ThamGiaOlympic.Where(o => o.SinhVienID == IDFilter));
                //var danhSachNghienCuuKhoaHoc = new ObservableCollection<NghienCuuKhoaHoc>(context.NghienCuuKhoaHoc.Where(n => n.SinhVienID == IDFilter));
                //var danhSachNhatKy = new ObservableCollection<NhatKy>(context.NhatKy.Where(n => n.SinhVienID == IDFilter));
                //var danhSachVanBang = new ObservableCollection<VanBangDuocNhan>(context.VanBangDuocNhan.Where(v => v.SinhVienID == IDFilter));
                //var danhSachHocBong = new ObservableCollection<HocBong>(context.HocBong.Where(h => h.SinhVienID == IDFilter));
                //var danhSachCauLacBo = new ObservableCollection<CauLacBoThamGia>(context.CauLacBoThamGia.Where(c => c.SinhVienID == IDFilter));
                //var danhSachHoatDongDoanThe = new ObservableCollection<HoatDongDoanTheThamGia>(context.HoatDongDoanTheThamGias.Where(h => h.SinhVienID == IDFilter));
                //var danhSachThayDoi = new ObservableCollection<ThayDoiTrongQuaTrinhHoc>(context.ThayDoiTrongQuaTrinhHoc.Where(t => t.SinhVienID == IDFilter));
                //var danhSachHoSoDaNop = new ObservableCollection<HoSoDaNop>(context.HoSoDaNop.Where(h => h.SinhVienID == IDFilter));

                //sinhvien.DanhSachCauLacBo = Converter.ConvertToCauLacBoThamGiaDTOList(danhSachCauLacBo);
                ////sinhvien.DanhSachHoatDongDoanThe = Converter.ConvertToHoat(danhSachCauLacBo);
                //sinhvien.DanhSachHocBong = Converter.ConvertToHocBongDTOList(danhSachHocBong);
                //sinhvien.DanhSachHoSoDaNop = Converter.ConvertToHoSoDaNopDTOList(danhSachHoSoDaNop);
                //sinhvien.DanhSachKhenThuong = Converter.ConvertToKhenThuongDTOList(danhSachKhenThuong);
                //sinhvien.DanhSachKyLuat = Converter.ConvertToKyLuatDTOList(danhSachKyLuat);
                //sinhvien.DanhSachNghienCuuKhoaHoc = Converter.ConvertToNghienCuuKhoaHocDTOList(danhSachNghienCuuKhoaHoc);
                //sinhvien.DanhSachNhatKy = Converter.ConvertToNhatKyDTOList(danhSachNhatKy);
                //sinhvien.DanhSachThamGiaOlympic = Converter.ConvertToThamGiaOlympicDTOList(danhSachThamGiaOlympic);
                ////sinhvien.DanhSachThayDoi = Converter.ConvertToThayDoi(danhSachThamGiaOlympic);
                //sinhvien.DanhSachVanBang = Converter.ConvertToVanBangDuocNhanDTOList(danhSachVanBang);

                return sinhvien;
            }
        }

        public SinhVienDetailDTO GetSinhVienDetailByMa(string MaFilter)
        {
            using (var context = container.Resolve<MainDataContainer>())
            {
                var result = context.SinhVien.Single(s => s.MaSinhVien == MaFilter);
                return Converter.ConvertToSinhVienDetail(result);
            }
        }

        public SinhVienDetailDTO GetSinhVienDetailByTen(string TenFilter)
        {
            using (var context = container.Resolve<MainDataContainer>())
            {
                var result = context.SinhVien.Single(s => s.TenSinhVien == TenFilter);
                return Converter.ConvertToSinhVienDetail(result);
            }
        }

        public void SaveSinhVien(SinhVienDetailDTO objdto)
        {
            var obj = Converter.ConvertToSinhVien(objdto);
            obj.NgayCapNhat = DateTime.Today;
            obj.NgayTao = DateTime.Today;
            
            using (var context = container.Resolve<MainDataContainer>())
            {
                var query = context.SinhVien.Single(s => s.ID == objdto.ID);
                obj.HinhAnh = query.HinhAnh;
                context.Detach(query);
                context.SinhVien.Attach(obj);
                if (obj.ID != 0)
                {
                    
                    
                    context.ObjectStateManager.ChangeObjectState(obj, EntityState.Modified);
                }
                context.SaveChanges();
            }
        }

        public void RemoveSinhVien(SinhVienDetailDTO objdto)
        {
            var obj = Converter.ConvertToSinhVien(objdto);
            using (var context = container.Resolve<MainDataContainer>())
            {
                context.SinhVien.Attach(obj);
                context.ObjectStateManager.ChangeObjectState(obj, EntityState.Deleted);
                context.SaveChanges();
            }
        }

        public void RemoveSinhVienByID(int IDFilter)
        {
            using (var context = container.Resolve<MainDataContainer>())
            {
                var obj = context.SinhVien.Single(h => h.ID == IDFilter);
                context.SinhVien.Attach(obj);
                context.ObjectStateManager.ChangeObjectState(obj, EntityState.Deleted);
                context.SaveChanges();
            }
        }
        #endregion

        #region ThamGiaOlympic Methods
        public ThamGiaOlympicDTO GetThamGiaOlympicByID(int IDFilter)
        {
            using (var context = container.Resolve<MainDataContainer>())
            {
                var result = context.ThamGiaOlympic.Single(t => t.ID == IDFilter);
                return Converter.ConvertToThamGiaOlympicDTO(result);
            }
        }

        public ObservableCollection<ThamGiaOlympicDTO> GetThamGiaOlympicBySinhVienID(int SinhVienIDFilter)
        {
            using (var context = container.Resolve<MainDataContainer>())
            {
                var result = context.ThamGiaOlympic.Where(t => t.SinhVienID == SinhVienIDFilter);
                var oc = new ObservableCollection<ThamGiaOlympic>(result);
                return Converter.ConvertToThamGiaOlympicDTOList(oc);
            }
        }

        public void SaveThamGiaOlympic(ThamGiaOlympicDTO objdto)
        {
            var obj = Converter.ConvertToThamGiaOlympic(objdto);
            using (var context = container.Resolve<MainDataContainer>())
            {
                var sinhVienQuery = context.SinhVien.Single(s => s.ID == objdto.SinhVienID);
                sinhVienQuery.ThamGiaOlympics.Add(obj);
                context.SaveChanges();
            }
        }

        public void SaveThamGiaOlympicList(ObservableCollection<ThamGiaOlympicDTO> dtolist)
        {
            var list = Converter.ConvertToThamGiaOlympicList(dtolist);
            using (var context = container.Resolve<MainDataContainer>())
            {
                foreach (var obj in list)
                {
                    context.ThamGiaOlympic.Attach(obj);
                    if (obj.ID != 0)
                    {
                        context.ObjectStateManager.ChangeObjectState(obj, EntityState.Modified);
                    }
                    else
                    {
                        context.ObjectStateManager.ChangeObjectState(obj, EntityState.Added);
                    }
                }
                context.SaveChanges();
            }
        }

        public void RemoveThamGiaOlympic(ThamGiaOlympicDTO objdto)
        {
            var obj = Converter.ConvertToThamGiaOlympic(objdto);
            using (var context = container.Resolve<MainDataContainer>())
            {
                context.ThamGiaOlympic.Attach(obj);
                context.ObjectStateManager.ChangeObjectState(obj, EntityState.Deleted);
                context.SaveChanges();
            }
        }

        public void RemoveThamGiaOlympicByID(int IDFilter)
        {
            using (var context = container.Resolve<MainDataContainer>())
            {
                var obj = context.ThamGiaOlympic.Single(t => t.ID == IDFilter);
                context.ThamGiaOlympic.Attach(obj);
                context.ObjectStateManager.ChangeObjectState(obj, EntityState.Deleted);
                context.SaveChanges();
            }
        }
        #endregion

        #region ThayDoiTrongQuaTrinhHoc Methods
        public ThayDoiTrongQuaTrinhHocDTO GetThayDoiTrongQuaTrinhHocByID(int IDFilter)
        {
            using (var context = container.Resolve<MainDataContainer>())
            {
                var result = context.ThayDoiTrongQuaTrinhHoc.Single(t => t.ID == IDFilter);
                return Converter.ConvertToThayDoiTrongQuaTrinhHocDTO(result);
            }
        }

        public ObservableCollection<ThayDoiTrongQuaTrinhHocDTO> GetThayDoiTrongQuaTrinhHocBySinhVienID(int SinhVienIDFilter)
        {
            using (var context = container.Resolve<MainDataContainer>())
            {
                var result = context.ThayDoiTrongQuaTrinhHoc.Where(t => t.SinhVienID == SinhVienIDFilter);
                var oc = new ObservableCollection<ThayDoiTrongQuaTrinhHoc>(result);
                return Converter.ConvertToThayDoiTrongQuaTrinhHocDTOList(oc);
            }
        }

        public void SaveThayDoiTrongQuaTrinhHoc(ThayDoiTrongQuaTrinhHocDTO objdto)
        {
            var obj = Converter.ConvertToThayDoiTrongQuaTrinhHoc(objdto);
            using (var context = container.Resolve<MainDataContainer>())
            {
                var sinhVienQuery = context.SinhVien.Single(s => s.ID == objdto.SinhVienID);
                sinhVienQuery.ThayDoiTrongQuaTrinhHocs.Add(obj);
                context.SaveChanges();
            }
        }

        public void SaveThayDoiTrongQuaTrinhHocList(ObservableCollection<ThayDoiTrongQuaTrinhHocDTO> dtolist)
        {
            var list = Converter.ConvertToThayDoiTrongQuaTrinhHocList(dtolist);
            using (var context = container.Resolve<MainDataContainer>())
            {
                foreach (var obj in list)
                {
                    context.ThayDoiTrongQuaTrinhHoc.Attach(obj);
                    if (obj.ID != 0)
                    {
                        context.ObjectStateManager.ChangeObjectState(obj, EntityState.Modified);
                    }
                    else
                    {
                        context.ObjectStateManager.ChangeObjectState(obj, EntityState.Added);
                    }
                }
                context.SaveChanges();
            }
        }

        public void RemoveThayDoiTrongQuaTrinhHoc(ThayDoiTrongQuaTrinhHocDTO objdto)
        {
            var obj = Converter.ConvertToThayDoiTrongQuaTrinhHoc(objdto);
            using (var context = container.Resolve<MainDataContainer>())
            {
                context.ThayDoiTrongQuaTrinhHoc.Attach(obj);
                context.ObjectStateManager.ChangeObjectState(obj, EntityState.Deleted);
                context.SaveChanges();
            }
        }

        public void RemoveThayDoiTrongQuaTrinhHocByID(int IDFilter)
        {
            using (var context = container.Resolve<MainDataContainer>())
            {
                var obj = context.ThayDoiTrongQuaTrinhHoc.Single(t => t.ID == IDFilter);
                context.ThayDoiTrongQuaTrinhHoc.Attach(obj);
                context.ObjectStateManager.ChangeObjectState(obj, EntityState.Deleted);
                context.SaveChanges();
            }
        }
        #endregion

        #region VanBangDuocNhan Methods
        public VanBangDuocNhanDTO GetVanBangDuocNhanByID(int IDFilter)
        {
            using (var context = container.Resolve<MainDataContainer>())
            {
                var result = context.VanBangDuocNhan.Include("LoaiVanBang").Single(v => v.ID == IDFilter);
                return Converter.ConvertToVanBangDuocNhanDTO(result);
            }
        }

        public ObservableCollection<VanBangDuocNhanDTO> GetVanBangDuocNhanBySinhVienID(int SinhVienIDFilter)
        {
            using (var context = container.Resolve<MainDataContainer>())
            {
                var result = context.VanBangDuocNhan.Include("LoaiVanBang").Where(v => v.SinhVien.ID == SinhVienIDFilter);
                var oc = new ObservableCollection<VanBangDuocNhan>(result);
                return Converter.ConvertToVanBangDuocNhanDTOList(oc);
            }
        }

        public void SaveVanBangDuocNhan(VanBangDuocNhanDTO objdto)
        {
            var obj = Converter.ConvertToVanBangDuocNhan(objdto);
            using (var context = container.Resolve<MainDataContainer>())
            {
                var sinhVienQuery = context.SinhVien.Single(s => s.ID == objdto.SinhVienID);
                sinhVienQuery.VanBangDuocNhans.Add(obj);
                context.SaveChanges();
            }
        }

        public void SaveVanBangDuocNhanList(ObservableCollection<VanBangDuocNhanDTO> dtolist)
        {
            var list = Converter.ConvertToVanBangDuocNhanList(dtolist);
            using (var context = container.Resolve<MainDataContainer>())
            {
                foreach (var obj in list)
                {
                    context.VanBangDuocNhan.Attach(obj);
                    if (obj.ID != 0)
                    {
                        context.ObjectStateManager.ChangeObjectState(obj, EntityState.Modified);
                    }
                    else
                    {
                        context.ObjectStateManager.ChangeObjectState(obj, EntityState.Added);
                    }
                }
                context.SaveChanges();
            }
        }

        public void RemoveVanBangDuocNhan(VanBangDuocNhanDTO objdto)
        {
            var obj = Converter.ConvertToVanBangDuocNhan(objdto);
            using (var context = container.Resolve<MainDataContainer>())
            {
                context.VanBangDuocNhan.Attach(obj);
                context.ObjectStateManager.ChangeObjectState(obj, EntityState.Deleted);
                context.SaveChanges();
            }
        }

        public void RemoveVanBangDuocNhanByID(int IDFilter)
        {
            using (var context = container.Resolve<MainDataContainer>())
            {
                var obj = context.VanBangDuocNhan.Single(t => t.ID == IDFilter);
                context.VanBangDuocNhan.Attach(obj);
                context.ObjectStateManager.ChangeObjectState(obj, EntityState.Deleted);
                context.SaveChanges();
            }
        }
        #endregion

        #region Private method
        private void Initialize()
        {
            DVHCConnection = EntityConnectionCreator.Instance.DonViHanhChinhConnection;
            mainDataConnection = EntityConnectionCreator.Instance.MainDataConnection;
            diemConnection = EntityConnectionCreator.Instance.DiemConnection;
            viewConnection = EntityConnectionCreator.Instance.ViewConnection;

            this.UnityRegisterType();
            this.InitializeMapper();
        }

        private void UnityRegisterType()
        {
            container.RegisterType<MainDataContainer>(new InjectionConstructor(mainDataConnection));
            container.RegisterType<VLUSMEntities>(new InjectionConstructor(viewConnection));
        }

        private void InitializeMapper()
        {
            Converter.ConfigureSinhVienMapping();
            Converter.ConfigureKyLuatMapping();
            Converter.ConfigureKhenThuongMapping();
            Converter.ConfigureNghienCuuKhoaHocMapping();
            Converter.ConfigureThamGiaOlympicMapping();
            Converter.ConfigureVanBangDuocNhanMapping();
            Converter.ConfigureHocBongMapping();
            Converter.ConfigureCauLacBoThamGiaMapping();
            Converter.ConfigureChinhSachMapping();
            Converter.ConfigureHoatDongDoanTheThamGiaMapping();
            Converter.ConfigureThayDoiTrongQuaTrinhHocMapping();
            Converter.ConfigureHoSoDaNopMapping();
        }
        #endregion

    }
}
